insert overwrite table jms_dm.dm_mng_thd_flase_send_upload_batch_mid_dt partition (dt)
select t1.bill_no          --运单号
     , t1.upload_batch     --批次号
     , t1.platform         --平台
     , t1.create_time      --手工上传时间
     , t2.input_time       --运单录入时间
     , t2.ordersource_code --订单来源
     , t2.ordersource_name --订单来源
     , from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as update_time
     , '{{ execution_date | cst_ds }}'                        as dt
from (
    select bill_no
         , upload_batch
         , platform
         , create_time
    from (
             select bill_no
                  , upload_batch
                  , platform
                  , create_time
                  , row_number() over (partition by bill_no order by create_time desc ) as rn1
             from jms_dwd.dwd_tab_false_ship_upload_det_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -31)
               and dt <= date_add('{{ execution_date | cst_ds }}', 29)
               and platform = '桃花岛春节不打烊'
         ) t
    where rn1 = 1
) t1
         left join
(
    select waybill_no, dt, input_time, ordersource_code, ordersource_name
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -45)
      and dt <= date_add('{{ execution_date | cst_ds }}', 0)
) t2
on t1.bill_no = t2.waybill_no
    distribute by dt
   , pmod(hash(bill_no), 10)
;
with unsign_summary_waybill as (
    select waybill_no                                                                     as waybill_no
         , input_time                                                                     as input_time
         , input_network_code                                                             as input_network_code
         , taking_later_scantime                                                          as taking_early_scantime
         , taking_later_scansitecode                                                      as taking_early_scansitecode
         , deliver_early_scantime                                                         as deliver_early_scantime
         , deliver_early_scansitecode                                                     as deliver_early_scansitecode
         , first_network_network_code                                                     as first_network_code
         , first_center_network_code                                                      as first_center_network_code
         , first_center_arrival_early_time                                                as first_center_arrival_early_time
         , first_center_send_later_time                                                   as first_center_send_later_time
         , second_center_network_code                                                     as second_center_network_code
         , second_center_arrival_early_time                                               as second_center_arrival_early_time
         , second_center_send_later_time                                                  as second_center_send_later_time
         , coalesce(instore_early_scantime, sign_early_scantime, outstore_early_scantime) as sign_time
         , dispatch_network_code                                                          as dispatch_network_code
         , end_center_network_code                                                        as end_center_network_code
         , ordersource_code                                                               as ordersource_code
         , ordersource_name                                                               as ordersource_name
    from jms_dwd.dwd_wide_unsign_summary_waybill_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -31)
      and dt <= '{{ execution_date | cst_ds }}'
      and substr(input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -31)
      and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
      and input_time is not null
--       and ordersource_name = '桃花岛'
--       and order_source_code = 'D09'
)
insert
overwrite
table
jms_dm.dm_mng_thd_flase_send_upload_batch_dt
partition
(
dt
)
select t1.bill_no                                             as waybill_no
     , t1.input_time                                          as input_time
     , substr(t1.input_time, 1, 10)                           as date_time
     , t2.first_network_code                                  as first_duty_network_code
     , t3.name                                                as first_duty_network_name
     , t3.network_type                                        as first_duty_network_type
     , t3.agent_code                                          as first_duty_agent_code
     , t3.agent_name                                          as first_duty_agent_name
     , 0                                                      as first_duty_type
     , 0                                                      as first_duty_standard
     , t1.input_time                                          as first_network_scantime
     , t2.first_network_code                                  as first_network_code
     , t3.name                                                as first_network_name
     , t3.agent_code                                          as first_agent_code
     , t3.agent_name                                          as first_agent_name
     , t3.city_id                                             as first_city_id
     , t3.city_desc                                           as first_city_desc
     , t3.provider_id                                         as first_province_id
     , t3.provider_desc                                       as first_province_desc
     , t2.dispatch_network_code                               as end_network_code
     , t4.name                                                as end_network_name
     , t4.agent_code                                          as end_agent_code
     , t4.agent_name                                          as end_agent_name
     , t4.city_id                                             as end_city_id
     , t4.city_desc                                           as end_city_desc
     , t4.provider_id                                         as end_province_id
     , t4.provider_desc                                       as end_province_desc
     , null                                                   as track_link
     , null                                                   as all_duty_network_code
     , null                                                   as all_duty_network_name
     , null                                                   as duration
     , t2.deliver_early_scansitecode                          as out_network_code
     , t6.name                                                as out_network_name
     , t6.agent_code                                          as out_city_id
     , t6.agent_name                                          as out_city_desc
     , t6.city_id                                             as out_province_id
     , t6.city_desc                                           as out_province_desc
     , t2.sign_time                                           as sign_time
     , null                                                   as all_duty_standard
     , null                                                   as all_duty_type
     , null                                                   as all_trace_falg
     , null                                                   as track_link_code
     , t1.upload_batch                                        as batch_no
     , 1                                                      as is_manual
     , from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as update_time
     , '{{ execution_date | cst_ds }}'                        as dt
from (
         select bill_no          --运单号
              , upload_batch     --批次号
              , platform         --平台
              , create_time      --手工上传时间
              , input_time       --运单录入时间
              , ordersource_code --订单来源
              , ordersource_name --订单来源
         from jms_dm.dm_mng_thd_flase_send_upload_batch_mid_dt
         where dt = '{{ execution_date | cst_ds }}'
           and substr(input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -31)
           and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
     ) t1
         left join unsign_summary_waybill t2
                   on t1.bill_no = t2.waybill_no
         left join jms_dim.dim_network_whole_massage t3
                   on t2.first_network_code = t3.code
         left join jms_dim.dim_network_whole_massage t4
                   on t2.dispatch_network_code = t4.code
         left join jms_dim.dim_network_whole_massage t6
                   on t2.deliver_early_scansitecode = t6.code
    distribute by dt;

insert
    overwrite
    table jms_dm.dm_mng_thd_flase_send_waybill_detail_dt
    partition
    (dt)
select t1.waybill_no
     , t1.input_time
     , t1.date_time
     , t1.first_duty_network_code
     , t1.first_duty_network_name
     , t1.first_duty_network_type
     , t1.first_duty_agent_code
     , t1.first_duty_agent_name
     , t1.first_duty_type
     , t1.first_duty_standard
     , t1.first_network_scantime
     , t1.first_network_code
     , t1.first_network_name
     , t1.first_agent_code
     , t1.first_agent_name
     , t1.first_city_id
     , t1.first_city_desc
     , t1.first_province_id
     , t1.first_province_desc
     , t1.end_network_code
     , t1.end_network_name
     , t1.end_agent_code
     , t1.end_agent_name
     , t1.end_city_id
     , t1.end_city_desc
     , t1.end_province_id
     , t1.end_province_desc
     , t1.track_link
     , t1.all_duty_network_code
     , t1.all_duty_network_name
     , t1.duration
     , t1.out_network_code
     , t1.out_network_name
     , t1.out_city_id
     , t1.out_city_desc
     , t1.out_province_id
     , t1.out_province_desc
     , t1.sign_time
     , t1.all_duty_standard
     , t1.all_duty_type
     , t1.all_trace_falg
     , t1.track_link_code
     , t1.batch_no
     , t1.is_manual
     , t1.update_time
     , substr(t1.input_time, 1, 10) as dt
from (
         select *
         from jms_dm.dm_mng_thd_flase_send_upload_batch_dt
         where dt = '{{ execution_date | cst_ds }}'
     ) t1
         left anti
         join
     (
         select waybill_no
         from jms_dm.dm_mng_thd_flase_send_waybill_detail_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -31)
           and dt <= '{{ execution_date | cst_ds }}'
     ) t2
     on t1.waybill_no = t2.waybill_no
union all
select t1.waybill_no
     , t1.input_time
     , t1.date_time
     , t1.first_duty_network_code
     , t1.first_duty_network_name
     , t1.first_duty_network_type
     , t1.first_duty_agent_code
     , t1.first_duty_agent_name
     , t1.first_duty_type
     , t1.first_duty_standard
     , t1.first_network_scantime
     , t1.first_network_code
     , t1.first_network_name
     , t1.first_agent_code
     , t1.first_agent_name
     , t1.first_city_id
     , t1.first_city_desc
     , t1.first_province_id
     , t1.first_province_desc
     , t1.end_network_code
     , t1.end_network_name
     , t1.end_agent_code
     , t1.end_agent_name
     , t1.end_city_id
     , t1.end_city_desc
     , t1.end_province_id
     , t1.end_province_desc
     , t1.track_link
     , t1.all_duty_network_code
     , t1.all_duty_network_name
     , t1.duration
     , t1.out_network_code
     , t1.out_network_name
     , t1.out_city_id
     , t1.out_city_desc
     , t1.out_province_id
     , t1.out_province_desc
     , t1.sign_time
     , t1.all_duty_standard
     , t1.all_duty_type
     , t1.all_trace_falg
     , t1.track_link_code
--      , if(t1.batch_no
     , if(t2.bill_no is not null, t2.upload_batch, t1.batch_no) as batch_no
     , if(t2.bill_no is not null, 1, t1.is_manual)              as is_manual
--      , t1.is_manual
     , t1.update_time
     , t1.dt
from (
    select *
    from jms_dm.dm_mng_thd_flase_send_waybill_detail_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -31)
      and dt <= '{{ execution_date | cst_ds }}'
) t1
         left join
(
    select bill_no
         , upload_batch
    from (
             select bill_no
                  , upload_batch
                  , platform
                  , create_time
                  , row_number() over (partition by bill_no order by create_time desc ) as rn1
             from jms_dwd.dwd_tab_false_ship_upload_det_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -31)
               and dt <= date_add('{{ execution_date | cst_ds }}', 29)
               and platform = '桃花岛春节不打烊'
         ) t
    where rn1 = 1
) t2
on t1.waybill_no = t2.bill_no
    distribute by dt
   , pmod(hash(waybill_no), 10)
;